Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OLAP_EXPRESSION_BOOL Example

The following script was used to create the view `awunits_view`, which is used in Example 31-1 to illustrate the use of `OLAP_EXPRESSION_BOOL`.

See Also: See "OLAP_TABLE Overview" for information about creating views of analytic workspaces.

Sample View: GLOBAL_AW.AWUNITS_VIEW

    -- Create the logical row
    CREATE TYPE awunits_row AS OBJECT (
                 awtime                  VARCHAR2(12),
                 awcustomer              VARCHAR2(30),
                 awproduct               VARCHAR2(30),
                 awchannel               VARCHAR2(30),
                 awunits                 NUMBER(16),
                 r2c                     RAW(32));
    /
    -- Create the logical table
    CREATE TYPE awunits_table AS TABLE OF awunits_row;
    /
    -- Create the view
    CREATE OR REPLACE VIEW awunits_view AS
       SELECT awunits,
              awtime, awcustomer, awproduct, awchannel, r2c
          FROM TABLE(OLAP_TABLE(
             'global_aw.globalaw DURATION SESSION',
             'awunits_table',
             '',
             'MEASURE awunits FROM units_cube_aw_units_aw
              DIMENSION awtime FROM time_aw WITH
                 HIERARCHY time_aw_parentrel
              DIMENSION awcustomer FROM customer_aw WITH
                 HIERARCHY customer_aw_parentrel
                           (customer_aw_hierlist ''MARKET_ROLLUP_AW'')
                    INHIERARCHY customer_aw_inhier
              DIMENSION awproduct FROM product_aw WITH
                 HIERARCHY product_aw_parentrel
              DIMENSION channel_aw WITH
                 HIERARCHY channel_aw_parentrel
                 ATTRIBUTE  awchannel FROM channel_aw_short_description
              ROW2CELL r2c'))
          WHERE awunits IS NOT NULL;
    /

The following query shows some of the aggregate data in the view. For all products in all markets during the year 2001, it shows the number of units sold through each channel.

    SQL> SELECT awchannel, awunits FROM awunits_view
         WHERE    awproduct = '1'
         AND      awcustomer = '7'
         AND      awtime = '4';

    AWCHANNEL            AWUNITS
    ---------            -------
    All Channels         415392
    Direct Sales         43783
    Catalog              315737
    Internet             55872

The following statements show the descriptions of the Product, Customer, and Time dimension members used in the query.

    SQL>execute dbms_aw.execute('limit product_aw to ''1''');
    SQL>execute dbms_aw.execute('rpr product_aw_short_description');

    PRODUCT_AW                 PRODUCT_AW_SHORT_DESCRIPTION
    --------------- --------------------------------------------------
    1               Total Product

    SQL>execute dbms_aw.execute('limit customer_aw to ''7''');
    SQL>execute dbms_aw.execute('rpr customer_aw_short_description');

    CUSTOMER_AW               CUSTOMER_AW_SHORT_DESCRIPTION
    --------------- --------------------------------------------------
    7               Total Market

    SQL>execute dbms_aw.execute('limit time_aw to ''4''');
    SQL>execute dbms_aw.execute('rpr time_aw_short_description');

    TIME_AW                     TIME_AW_SHORT_DESCRIPTION
    --------------- --------------------------------------------------
    4               2001

Example 31-1 OLAP_EXPRESSION_BOOL Function in a SELECT List

This example uses the view described in "Sample View: GLOBAL_AW.AWUNITS_VIEW".

The following `SELECT` statement calculates an expression with an alias of `lowest_units`, which indicates whether or not the number of units of each product was less than 500.

    SQL>SELECT awproduct products,
           olap_expression_bool(r2c, 'units_cube_aw_units_aw le 500') lowest_units
              FROM awunits_view
                   WHERE     awproduct > 39
                   AND       awproduct < 46
                   AND       awcustomer = '7'
                   AND       awchannel = 'Internet'
                   AND       awtime = '4';

    PRODUCTS        LOWEST_UNITS
    --------------- ------------
    40              0
    41              1
    42              1
    43              1
    44              1
    45              0

This query shows that products 41-44 all had less than 500 units. These products are the documentation sets in German, French, Spanish, and Italian. The selected products are shown as follows.

    SQL>execute dbms_aw.execute
               ('limit product_aw to product_aw gt 39 and product_aw lt 46');
    SQL>execute dbms_aw.execute('rpr product_aw_short_description');

    PRODUCT_AW                 PRODUCT_AW_SHORT_DESCRIPTION
    --------------- --------------------------------------------------
    40              O/S Documentation Set - English
    41              O/S Documentation Set - German
    42              O/S Documentation Set - French
    43              O/S Documentation Set - Spanish
    44              O/S Documentation Set - Italian
    45              O/S Documentation Set - Kanji

# OLAP_EXPRESSION_BOOL Syntax

The `OLAP_EXPRESSION_BOOL` function dynamically executes an OLAP DML boolean expression within the context of an `OLAP_TABLE` function.

Syntax

    OLAP_EXPRESSION_BOOL(
              r2c                  IN   RAW(32),
              boolean_expression   IN   VARCHAR2)
          RETURN NUMBER;

Parameters

Table 31-1 OLAP_EXPRESSION_BOOL Function Parameters

Parameter | Description
--- | ---
`r2c` | The name of a column populated by a `ROW2CELL` clause in a call to `OLAP_TABLE`. `ROW2CELL` is a component of a limit map parameter of the `OLAP_TABLE` function. See "Limit Map Parameter".
`boolean_expression` | A boolean calculation that will be performed in the analytic workspace. Search for "boolean expression" in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".

Returns

An evaluation of boolean_expression for each row of the table object returned by the `OLAP_TABLE` function.

`OLAP_EXPRESSION_BOOL` returns boolean data. To return numeric, date, or text data, use the `OLAP_EXPRESSION`, `OLAP_EXPRESSION_DATE`, or `OLAP_EXPRESSION_TEXT` functions.

Example

Refer to "OLAP_EXPRESSION" for more examples of OLAP single-row functions.